﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class FLTEKGBData : BaseFunction
    {
        
        public FLTEKGBData()
            : base()
        {

        }
        public FLTEKGBData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetVKPOS(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "VKPOS";
            string SUPPLIER = dwABPOS["SUPPLIER"].ToString().Trim();
            string FROMDATE = dwABPOS["fromdate"].ToString().Trim();
            string ENDDATE = dwABPOS["enddate"].ToString().Trim();
            /*"SELECT * FROM CO_DELAY_REASON A "
                   + " WHERE (TO_NUMBER(A.ORDERNO) = '" + ORDER + "' OR '" + ORDER + "' = '999999') "
                   + " AND (TRIM(A.ARTICLE) = '" + ARTICLE + "' OR '" + ARTICLE + "' = 'AAA') "
                   + " AND A.DELDATE >='" + FROMDATE + "' AND A.DELDATE <= '" + ENDDATE + "' "
                   + " AND (TO_NUMBER(A.CUSTOMERCODE) = '" + CUSTOMER + "' OR '" + CUSTOMER + "'='99999999') "
                   + " ORDER BY 1,5,6 " */
            
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT A.GABBUCHDAT,B.GPPARTIE,B.GPSPPART,B.GPFBPART, "
                    + " A.GABAUFKRS||'-'||TO_NUMBER(A.GABAUFLFD)||'-'||TO_NUMBER(A.GABAUFPOS) AS ORDERNO, "
                    + " E.LSLIEFNR,TRIM(E.LSNAME1) AS SUPPLIER, "
                    + " A.GABART,A.GABFARBE,D.GSAGARNNR, "
                    + " SUM(A.GABKG*0.001)OVER(PARTITION BY A.GABBUCHDAT,B.GPPARTIE) AS QTY "
                    + " FROM GABEW A,GPDAT B,EKPOS C,GSDAT D,LIEFDAT E "
                    + " WHERE GABAUFTYP = 'B'  "
                    + " AND A.GABMANDANT = B.GPMANDANT AND A.GABPARTIE = B.GPPARTIE "
                    + " AND A.GABPARTIEKZ = B.GPPARTIEKZ AND A.GABMANDANT = C.EKPMANDANT "
                    + " AND A.GABAUFKRS = C.EKPAUFKRS AND A.GABAUFLFD = C.EKPAUFLFD "
                    + " AND A.GABAUFPOS = C.EKPAUFPOS AND C.EKPSA = 'PO' "
                    + " AND A.GABMANDANT = D.GSMANDANT AND A.GABART = D.GSART "
                    + " AND A.GABVAR = D.GSVAR AND C.EKPMANDANT = E.LSMANDANT "
                    + " AND C.EKPLIEFNR = E.LSLIEFNR "
                    + " AND (TO_NUMBER(E.LSLIEFNR) = '" + SUPPLIER + "' OR '" + SUPPLIER + "'='99999999') "
                    + " AND A.GABBUCHDAT >='" + FROMDATE + "' AND A.GABBUCHDAT <= '" + ENDDATE + "' "
                    + " UNION ALL "
                    + " SELECT DISTINCT A.GABBUCHDAT,B.GPPARTIE,B.GPSPPART,B.GPFBPART, "
                    + " A.GABAUFKRS||'-'||TO_NUMBER(A.GABAUFLFD)||'-'||TO_NUMBER(A.GABAUFPOS) AS ORDERNO, "
                    + " E.LSLIEFNR,TRIM(E.LSNAME1) AS SUPPLIER, "
                    + " A.GABART,A.GABFARBE,D.GSAGARNNR, "
                    + " SUM(-A.GABKG*0.001)OVER(PARTITION BY A.GABBUCHDAT,B.GPPARTIE) AS QTY "
                    + " FROM GABEW A,GPDAT B,EKPOS C,GSDAT D,LIEFDAT E "
                    +" WHERE A.GABBS='33' AND A.GABKSTKRS = '  ' AND A.GABKST = '0401' "
                    + " AND A.GABMANDANT = B.GPMANDANT AND A.GABPARTIE = B.GPPARTIE "
                    + " AND A.GABPARTIEKZ = B.GPPARTIEKZ AND B.GPMANDANT = C.EKPMANDANT "
                    + " AND B.GPBESTKRS = C.EKPAUFKRS AND B.GPBESTLFD = C.EKPAUFLFD "
                    + " AND B.GPBESTPOS = C.EKPAUFPOS AND C.EKPSA = 'PO' "
                    + " AND A.GABMANDANT = D.GSMANDANT AND A.GABART = D.GSART "
                    + " AND A.GABVAR = D.GSVAR AND C.EKPMANDANT = E.LSMANDANT "
                    + " AND C.EKPLIEFNR = E.LSLIEFNR   "
                    + " AND (TO_NUMBER(E.LSLIEFNR) = '" + SUPPLIER + "' OR '" + SUPPLIER + "'='99999999') "
                    + " AND A.GABBUCHDAT >='" + FROMDATE + "' AND A.GABBUCHDAT <= '" + ENDDATE + "' "
                    + " ORDER BY 2,1 "                   
                    ;
                
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["SUPPLIER"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["SUPPLIER"] = value;
                         
                        //GetREMARK(dw);
                    }
                }                
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        
        public DataSet BuildFLTEKGBData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetVKPOS(dwUI);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        
    }
}